package edu.uwm.universitydb.tests;

public class Query5 extends PerformanceTest {


	@Override
	public String getName() {
		return "Department with most students working on projects";
	}

	@Override
	public String[] getAddIndex() {
		return new String[] {
				"create index student_index on grad_student (grad_ssn) using hash;",
				"create index prof_index on professor (prof_ssn) using hash;",
				"create index proj_index on project (pno) using hash;"
			};
	}

	@Override
	public String[] getDropIndex() {
		return new String[] {
				"drop index student_index on grad_student",
				"drop index prof_index on professor",
				"drop index proj_index on project"
		};
	}

	@Override
	public String getQuery() {
		return "SELECT deptname, max(stcount) " +
				"FROM ( " +
				"	SELECT dept.name as deptname, COUNT(*) as stcount " +
				"	FROM grad_student grad, department dept, project pr, supervises sup " +
				"	WHERE dept.dno = grad.dno AND pr.pno = sup.pno " +
				"	AND sup.grad_ssn = grad.grad_ssn " +
				"	GROUP BY dept.dno) as deptcount ";
	}
}
 
